import pandas as pd
import argparse
from datetime import datetime

# 获取当前日期
current_date = datetime.now()

# 计算年份和第几周
year = current_date.year
week_num = current_date.isocalendar()[1] -1  # ISO标准的周数

def generate_dashboard(input_file, output_file=None):
    # Read Excel file
    df = pd.read_excel(input_file)

    # Calculate key metrics
    total_tickets = len(df)
    resolved_tickets = len(df[df['工单状态'].isin(['Resolved', 'Closed'])])
    open_tickets = len(df[df['工单状态'] == 'Open'])

    # 计算工单耗时的总和并四舍五入到小数点后两位
    total_hours = round(df['工单耗时'].sum(), 2)

    # Staff statistics
    staff_stats = df.groupby('运维人员').agg({
        '工单耗时': 'sum',
        '工单单号': 'count'
    }).reset_index()

    # 按工单数量降序排序
    staff_stats = staff_stats.sort_values('工单单号', ascending=False).reset_index(drop=True)

    # Project statistics
    project_stats = df['项目名称'].value_counts().head(3).reset_index()
    project_stats.columns = ['project_name', 'ticket_count']

    # Task type statistics
    task_type_stats = df['任务类型'].value_counts().reset_index()
    task_type_stats.columns = ['task_type', 'count']

    # Open tickets
    open_tickets_df = df[df['工单状态'] == 'Open'][['项目名称', '工单主题', '工单单号', '运维人员', '创建时间']]

    # Closed tickets
    # 筛选状态为 'Resolved' 或 'Closed' 的工单
    closed_tickets_df = df[(df['工单状态'] == 'Resolved') | (df['工单状态'] == 'Closed')][
        ['项目名称', '工单主题', '工单单号', '运维人员', '创建时间']]

    # Generate HTML
    # <script src="js/echarts.min.js"></script>
    html_template = f"""
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>{year}年第{week_num}周运维工作数据看板</title>
    <script src="http://bi.egrant.cn/js/echarts.min.js"></script>
    
    <style>
        body {{
            font-family: 'Arial', sans-serif;
            margin: 0;
            padding: 20px;
            background-color: #f5f7fa;
            color: #333;
        }}
        .dashboard {{
            max-width: 1200px;
            margin: 0 auto;
            display: grid;
            grid-template-columns: repeat(2, 1fr);
            gap: 20px;
        }}
        .card {{
            background: white;
            border-radius: 10px;
            box-shadow: 0 4px 12px rgba(0, 0, 0, 0.05);
            padding: 20px;
        }}
        .card-header {{
            font-size: 18px;
            font-weight: bold;
            margin-bottom: 15px;
            color: #2c3e50;
            border-bottom: 1px solid #eee;
            padding-bottom: 10px;
        }}
        .chart-container {{
            width: 100%;
            height: 300px;
        }}
        .summary {{
            grid-column: span 2;
            background: linear-gradient(135deg, #6a11cb 0%, #2575fc 100%);
            color: white;
            padding: 25px;
            border-radius: 10px;
        }}
        .summary-title {{
            font-size: 22px;
            margin-bottom: 15px;
            font-weight: bold;
        }}
        .summary-content {{
            display: grid;
            grid-template-columns: repeat(4, 1fr);
            gap: 15px;
        }}
        .summary-item {{
            text-align: center;
        }}
        .summary-value {{
            font-size: 28px;
            font-weight: bold;
            margin: 5px 0;
        }}
        .summary-label {{
            font-size: 14px;
            opacity: 0.9;
        }}
        .insight-card {{
            grid-column: span 2;
            background: white;
            border-radius: 10px;
            box-shadow: 0 4px 12px rgba(0, 0, 0, 0.05);
            padding: 20px;
        }}
        .insight-title {{
            font-size: 18px;
            font-weight: bold;
            margin-bottom: 15px;
            color: #2c3e50;
        }}
        .insight-content {{
            line-height: 1.6;
        }}
        .data-table {{
            width: 100%;
            border-collapse: collapse;
            margin-top: 15px;
        }}
        .data-table th, .data-table td {{
            border: 1px solid #ddd;
            padding: 8px;
            text-align: left;
        }}
        .data-table th {{
            background-color: #f2f2f2;
        }}
        .data-table tr:nth-child(even) {{
            background-color: #f9f9f9;
        }}
    </style>
</head>
<body>
    <div class="dashboard">
        <div class="summary">
            <div class="summary-title" style="text-align: left;">{year}年第{week_num}周系统运维工作关键指标</div>
            <div class="summary-content">
                <div class="summary-item">
                    <div class="summary-value">{total_tickets}</div>
                    <div class="summary-label">总工单数量</div>
                </div>
                <div class="summary-item">
                    <div class="summary-value">{resolved_tickets}</div>
                    <div class="summary-label">已解决工单</div>
                </div>
                <div class="summary-item">
                    <div class="summary-value">{open_tickets}</div>
                    <div class="summary-label">未完成工单</div>
                </div>
                <div class="summary-item">
                    <div class="summary-value">{total_hours}</div>
                    <div class="summary-label">总工时(小时)</div>
                </div>
            </div>
        </div>

        <div class="card">
            <div class="card-header">工单状态分布</div>
            <div class="chart-container" id="statusChart"></div>
        </div>

        <div class="card">
            <div class="card-header">运维人员工作量对比</div>
            <div class="chart-container" id="workloadChart"></div>
        </div>

        <div class="card">
            <div class="card-header">项目工单数量TOP3</div>
            <div class="chart-container" id="projectChart"></div>
        </div>

        <div class="card">
            <div class="card-header">任务类型分布</div>
            <div class="chart-container" id="taskTypeChart"></div>
        </div>

        <div class="insight-card">
            <div class="insight-title">关键数据分析</div>
            <div class="insight-content">
                <p><strong>1. 项目分布分析</strong></p>
                <p>• {project_stats.iloc[0]['project_name']}工单最多[{project_stats.iloc[0]['ticket_count']}个]，占总工单量的&nbsp{(project_stats.iloc[0]['ticket_count'] / total_tickets * 100):.1f}%</p>                   
                <p>• {project_stats.iloc[1]['project_name']}和{project_stats.iloc[2]['project_name']}是第二活跃项目</p>
                <p>• {project_stats.iloc[1]['project_name']}工单   [{project_stats.iloc[1]['ticket_count']}个]，占总工单量的&nbsp{(project_stats.iloc[1]['ticket_count'] / total_tickets * 100):.1f}%</p>
                <p>• {project_stats.iloc[2]['project_name']}工单   [{project_stats.iloc[2]['ticket_count']}个]，占总工单量的&nbsp{(project_stats.iloc[2]['ticket_count'] / total_tickets * 100):.1f}%</p>
                
                <p><strong>2. 人员工作分析</strong></p>
                <p>• {staff_stats.iloc[0]['运维人员']}处理工单最多,共计[{staff_stats.iloc[0]['工单单号']}个]</p>
                <p>• {staff_stats.iloc[1]['运维人员']}处理工单[{staff_stats.iloc[1]['工单单号']}个]</p>
                <p>• {staff_stats.iloc[0]['运维人员']}平均每个工单耗时&nbsp;{(staff_stats.iloc[0]['工单耗时'] / staff_stats.iloc[0]['工单单号']):.2f}&nbsp;小时，效率最高</p>
                <p>• {staff_stats.iloc[1]['运维人员']}平均每个工单耗时&nbsp;{(staff_stats.iloc[1]['工单耗时'] / staff_stats.iloc[1]['工单单号']):.2f}&nbsp;小时</p>
                
                <p><strong>3. 工单状态分析</strong></p>
                <p>• {(resolved_tickets / total_tickets * 100):.1f}%&nbsp工单已完成，{(open_tickets / total_tickets * 100):.1f}%&nbsp工单未完成</p>
            </div>
        </div>

        <div class="card" style="grid-column: span 2;">
            <div class="card-header">未完成工单明细</div>
            <table class="data-table">
                <thead>
                    <tr>
                        <th>项目名称</th>
                        <th>工单主题</th>
                        <th>工单单号</th>
                        <th>运维人员</th>
                        <th>创建时间</th>
                    </tr>
                </thead>
                <tbody>
                    {"".join([f'''
                    <tr>
                        <td>{row['项目名称']}</td>
                        <td>{row['工单主题']}</td>
                        <td>{row['工单单号']}</td>
                        <td>{row['运维人员']}</td>
                        <td>{row['创建时间']}</td>
                    </tr>
                    ''' for _, row in open_tickets_df.iterrows()])}
                </tbody>
            </table>
        </div>

        <div class="card" style="grid-column: span 2;">
            <div class="card-header">已完成工单明细</div>
            <table class="data-table">
                <thead>
                    <tr>
                        <th>项目名称</th>
                        <th>工单主题</th>
                        <th>工单单号</th>
                        <th>运维人员</th>
                        <th>创建时间</th>
                    </tr>
                </thead>
                <tbody>
                    {"".join([f'''
                    <tr>
                        <td>{row['项目名称']}</td>
                        <td>{row['工单主题']}</td>
                        <td>{row['工单单号']}</td>
                        <td>{row['运维人员']}</td>
                        <td>{row['创建时间']}</td>
                    </tr>
                    ''' for _, row in closed_tickets_df.iterrows()])}
                </tbody>
            </table>
        </div>

    </div>

    <script>
        // 工单状态分布
        const statusChart = echarts.init(document.getElementById('statusChart'));
        statusChart.setOption({{
            tooltip: {{
                trigger: 'item',
                formatter: '{{a}} <br/>{{b}}: {{c}} ({{d}}%)'
            }},
            legend: {{
                orient: 'vertical',
                right: 10,
                top: 'center',
                data: ['未完成', '已完成']
            }},
            series: [
                {{
                    name: '工单状态',
                    type: 'pie',
                    radius: ['50%', '70%'],
                    avoidLabelOverlap: false,
                    label: {{
                        show: false,
                        position: 'center'
                    }},
                    emphasis: {{
                        label: {{
                            show: true,
                            fontSize: '18',
                            fontWeight: 'bold'
                        }}
                    }},
                    labelLine: {{
                        show: false
                    }},
                    data: [

                        {{ value: {open_tickets}, name: '未完成' }},
                        {{ value: {resolved_tickets}, name: '已完成' }},
                    ],
                    color: ['#ff6384','#36a2eb']
                }}
            ]
        }});

        // 运维人员工作量对比
        const workloadChart = echarts.init(document.getElementById('workloadChart'));
        workloadChart.setOption({{
            tooltip: {{
                trigger: 'axis',
                axisPointer: {{
                    type: 'shadow'
                }}
            }},
            grid: {{
                left: '3%',
                right: '4%',
                bottom: '3%',
                containLabel: true
            }},
            xAxis: {{
                type: 'value'
            }},
            yAxis: {{
                type: 'category',
                data: {staff_stats['运维人员'].to_list()}
            }},
            series: [
                {{
                    name: '工单数量',
                    type: 'bar',
                    data: {staff_stats['工单单号'].to_list()},
                    itemStyle: {{
                        color: '#36a2eb'
                    }}
                }},
                {{
                    name: '工时(小时)',
                    type: 'bar',
                    data: {staff_stats['工单耗时'].to_list()},
                    itemStyle: {{
                        color: '#ffce56'
                    }}
                }}
            ]
        }});

        // 项目工单数量TOP3
        const projectChart = echarts.init(document.getElementById('projectChart'));
        projectChart.setOption({{
            tooltip: {{
                trigger: 'axis',
                axisPointer: {{
                    type: 'shadow'
                }}
            }},
            grid: {{
                left: '3%',
                right: '4%',
                bottom: '3%',
                containLabel: true
            }},
            xAxis: {{
                type: 'category',
                data: {project_stats['project_name'].to_list()}
            }},
            yAxis: {{
                type: 'value'
            }},
            series: [
                {{
                    data: {project_stats['ticket_count'].to_list()},
                    type: 'bar',
                    showBackground: true,
                    backgroundStyle: {{
                        color: 'rgba(180, 180, 180, 0.2)'
                    }},
                    itemStyle: {{
                        color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [
                            {{ offset: 0, color: '#83bff6' }},
                            {{ offset: 0.5, color: '#188df0' }},
                            {{ offset: 1, color: '#188df0' }}
                        ])
                    }}
                }}
            ]
        }});

        // 任务类型分布
        const taskTypeChart = echarts.init(document.getElementById('taskTypeChart'));
        taskTypeChart.setOption({{
            tooltip: {{
                trigger: 'item',
                formatter: '{{a}} <br/>{{b}}: {{c}} ({{d}}%)'
            }},
            series: [
                {{
                    name: '任务类型',
                    type: 'pie',
                    radius: ['30%', '70%'],
                    roseType: 'radius',
                    itemStyle: {{
                        borderRadius: 5,
                        borderColor: '#fff',
                        borderWidth: 2
                    }},
                    label: {{
                        show: true,
                        formatter: '{{b}}: {{c}}'
                    }},
                    data: [
                        {{ value: {task_type_stats[task_type_stats['task_type'] == '日常任务']['count'].values[0] if not task_type_stats[task_type_stats['task_type'] == '日常任务'].empty else 0}, name: '日常任务' }},
                        {{ value: {task_type_stats[task_type_stats['task_type'] == '周期任务']['count'].values[0] if not task_type_stats[task_type_stats['task_type'] == '周期任务'].empty else 0}, name: '周期任务' }}
                    ],
                    color: ['#36a2eb', '#ffce56']
                }}
            ]
        }});

        // 响应式调整
        window.addEventListener('resize', function() {{
            statusChart.resize();
            workloadChart.resize();
            projectChart.resize();
            taskTypeChart.resize();
        }});
    </script>
</body>
</html>
"""

    # Save HTML file
    if not output_file:
        output_file = input_file.replace('.xlsx', '_dashboard.html')
    with open(output_file, 'w', encoding='utf-8') as f:
        f.write(html_template)

    print(f"数据看板已生成: {output_file}")

if __name__ == "__main__":
    parser = argparse.ArgumentParser(description='生成运维工作周报数据看板')
    input_file1 = r"D:\check\issues.xlsx"  # 使用原始字符串避免转义问题
    output_file2 = r"D:\check\issues-dashboard.html"  # 可选

    generate_dashboard(input_file1, output_file2)
